package org.example.judge_common.util;


import com.alibaba.fastjson.JSON;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.example.judge_common.annotation.ExcelCellName;
import org.example.judge_common.hander.RetrunMsgException;
import org.example.judge_common.rank.RankList;
import org.example.judge_common.rank.RankUser;
import org.example.judge_pojo.ContestRecord;

import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

@Slf4j
public class ExcelUtils {
    public static String pattern = "yyyy/MM/dd HH:mm:ss";
    public static <T> List<T> getList(InputStream inputStream, Class<T> clazz) throws RetrunMsgException {

        Workbook wb = null;
        try {
            wb = new XSSFWorkbook(inputStream);
        } catch (IOException e) {

            throw new RetrunMsgException("文件格式错误：需为xlsx格式文件");
        }
        List<T> res = new ArrayList<>();
        //获取表单
        Sheet sheet = wb.getSheetAt(0);
        Row row = sheet.getRow(sheet.getFirstRowNum());   //获取表头名称的行
        Cell cell = null;
        short cellBegin = row.getFirstCellNum(),cellEnd = row.getLastCellNum();
        Field[] cellFields = new Field[cellEnd-cellBegin+1];
        Field[] fields = clazz.getDeclaredFields();
        fields = getExist(fields);
        //获取需要用到的属性名
        for (Field field : fields) {
            field.setAccessible(true);
        }
        for(short j = cellBegin;j< cellEnd;j++){
            cell = row.getCell(j);
            String value = cell.getStringCellValue();  //列名
            for (Field field : fields) {

                ExcelCellName cellName = field.getDeclaredAnnotation(ExcelCellName.class);
                if(cellName!=null&&value.equals(cellName.value())){  //列名和注解中的信息匹配
                    cellFields[j-cellBegin] = field;

                }
                if(field.getName().equals(value)){  //列名和属性名也匹配成功
                    cellFields[j-cellBegin] = field;
                }
            }
            if(cellFields[j-cellBegin]==null) log.debug("存在不能识别或exist=false的列名：{}",value);
            //System.out.println(cellFields[j-cellBegin].getName());
        }
        //开始处理数据
        for (int i = sheet.getFirstRowNum()+1; i <= sheet.getLastRowNum(); i++) {  //数据开始的行范围
            row = sheet.getRow(i);   //行
            T obj = null;  //实例化对象
            try {
                obj = clazz.newInstance();
            } catch (InstantiationException | IllegalAccessException e) {
                e.printStackTrace();
                throw new RetrunMsgException("对象实例化失败:是否存在无参构造方法");
            }
            for(short j = cellBegin;j< cellEnd;j++) {
                if (cellFields[j-cellBegin]==null) {   //未匹配到对应的列
                    continue;
                }
                cell = row.getCell(j); //列
                Object value = getValue(cell,cellFields[j-cellBegin]);

                try {
                    if(value!=null)cellFields[j-cellBegin].set(obj,value);
                } catch (IllegalAccessException e) {
                    throw new RetrunMsgException(e);
                }
            }
            res.add(obj);
        }

        try {
            wb.close();
        } catch (IOException e) {
            e.printStackTrace();
        }

        return res;
    }

    /**
     * 去除 field中的exist为false的field
     * @param fields
     * @return
     */
    private static Field[] getExist(Field[] fields) {
        List<Field> res = new ArrayList<>();
        for (Field field : fields) {
            ExcelCellName cellName = field.getDeclaredAnnotation(ExcelCellName.class);
            if(cellName==null||cellName.exist()){  //列名和注解中的信息匹配
                res.add(field);
            }
        }
        return res.toArray(new Field[0]);
    }

    //获取单元格结果
    private static Object getValue(Cell cell,Field field) throws RetrunMsgException {

        Object res = null;
        if(cell==null) {
            return res;
        }
        CellType type = cell.getCellType();
        if(type.equals(CellType.STRING)){   //string
            res = cell.getStringCellValue();
        }else if(type.equals(CellType.BOOLEAN)){  //boolean
            res = cell.getBooleanCellValue();
        }else if(type.equals(CellType.NUMERIC)){  //数字或日期
            if (DateUtil.isCellDateFormatted(cell)) {  //日期
               res = cell.getDateCellValue();
            }else{    //数字
                res = cell.getNumericCellValue();




            }
        }else {
            throw new RetrunMsgException("存在未使用的对应类型的属性变量:"+type.toString());
        }
        //double 类型转为int或short...

        if(field!=null){
            if(res instanceof Double &&field.getType()!=double.class&&field.getType()!=Double.class){
                if(field.getType()==String.class){  //字符串类型，避免字符串误判为double类型
                    if(((Double) res).longValue()== ((Double) res).doubleValue()){ //可以去掉末尾零
                        res = res.toString().substring(0,res.toString().indexOf("."));
                    }
                    res = res.toString();  //转换为string
                }else if(field.getType()==Float.class||field.getType()==float.class){
                    res = ((Double) res).floatValue();
                }else if(field.getType()==Byte.class||field.getType()==byte.class){
                    res = ((Double) res).byteValue();
                }else if(field.getType()==Short.class||field.getType()==short.class){
                    res = ((Double) res).shortValue();
                }else if(field.getType()==Long.class||field.getType()==long.class){
                    res = ((Double) res).longValue();
                }else{  //统统转为int
                    //System.out.println(cellFields[j-cellBegin].getType());
                    res = ((Double) res).intValue();
                }
            }else if(res instanceof String && field.getType()!=String.class){  //String类型转为其他
                if(field.getType()==Double.class||field.getType()==double.class){
                    res = Double.valueOf(res.toString());
                }else if(field.getType()==Float.class||field.getType()==float.class){
                    res = Float.valueOf(res.toString());
                }else if(field.getType()==Byte.class||field.getType()==byte.class){
                    res = Byte.valueOf(res.toString());
                }else if(field.getType()==Short.class||field.getType()==short.class){
                    res = Short.valueOf(res.toString());
                }else if(field.getType()==Long.class||field.getType()==long.class){
                    res = Long.valueOf(res.toString());
                }else if(field.getType()==Integer.class||field.getType()==int.class){  //转为int
                    res = Integer.valueOf(res.toString());
                }else if(field.getType()==Date.class){
                    try {
                        res = new SimpleDateFormat(pattern).parse(res.toString());

                    } catch (ParseException e) {
                        log.debug("默认日期格式转换失败，再次使用JSON尝试");
                        try{
                            res = JSON.parseObject(res.toString(),Date.class);
                        }catch (Exception e1){
                            log.debug("日期转换错误,错误信息：{}",e1.getMessage());
                            throw new RetrunMsgException("文件中日期格式错误,正确格式："+pattern);
                        }

                    }

                }else if(field.getType()==Boolean.class||field.getType()==boolean.class){
                    res = Boolean.valueOf(res.toString());
                } else{
                    throw new RetrunMsgException("存在不确认的列名类型");
                }
            }
        }

        return res;
    }

    /**
     * 榜单
     * @param rank
     * @return
     * @throws RetrunMsgException
     */
    public static  Workbook getExcel(RankList rank) throws RetrunMsgException{
        //工作簿
        Workbook wb = new SXSSFWorkbook();    //大数据
        Sheet sheet = wb.createSheet(rank.getContestName());
        sheet.setDefaultRowHeight((short) (3*256));

        CellStyle cellStyle = wb.createCellStyle();
        int rowInt = 0;
        Row row = sheet.createRow(rowInt++);
        //写入列名
        //设置背景色
        cellStyle.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());
        //必须设置 否则背景色不生效
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);//增加水平居中样式
        cellStyle.setVerticalAlignment(org.apache.poi.ss.usermodel.VerticalAlignment.CENTER);//增加垂直居中样式

        //一血
        CellStyle cellStyleFirst = wb.createCellStyle();
        cellStyleFirst.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());
        cellStyleFirst.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyleFirst.setAlignment(HorizontalAlignment.CENTER);//增加水平居中样式
        cellStyleFirst.setVerticalAlignment(org.apache.poi.ss.usermodel.VerticalAlignment.CENTER);//增加垂直居中样式
        cellStyleFirst.setWrapText(true);//自动换行样式增加
        //通过
        CellStyle cellStyleAc = wb.createCellStyle();
        cellStyleAc.setFillForegroundColor(IndexedColors.GREEN.getIndex());
        cellStyleAc.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyleAc.setAlignment(HorizontalAlignment.CENTER);//增加水平居中样式
        cellStyleAc.setVerticalAlignment(org.apache.poi.ss.usermodel.VerticalAlignment.CENTER);//增加垂直居中样式
        cellStyleAc.setWrapText(true);//自动换行样式增加
        //错误
        CellStyle cellStyleWa = wb.createCellStyle();
        cellStyleWa.setFillForegroundColor(IndexedColors.RED.getIndex());
        cellStyleWa.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyleWa.setAlignment(HorizontalAlignment.CENTER);//增加水平居中样式
        cellStyleWa.setVerticalAlignment(org.apache.poi.ss.usermodel.VerticalAlignment.CENTER);//增加垂直居中样式

        int colInt = 0;
        Cell cell = row.createCell(colInt++, CellType.STRING); //名次
        cell.setCellValue("名次");
        cell.setCellStyle(cellStyle);


        cell = row.createCell(colInt++, CellType.STRING); //参赛者
        cell.setCellValue("参赛者");
        cell.setCellStyle(cellStyle);
        sheet.setColumnWidth(colInt-1, 20*256);//参赛者
        cell = row.createCell(colInt++, CellType.STRING); //学校
        cell.setCellValue("学校");
        cell.setCellStyle(cellStyle);
        sheet.setColumnWidth(colInt-1, 25*256);//学校宽度

        cell = row.createCell(colInt++, CellType.STRING); //过题数
        cell.setCellValue("通过");
        cell.setCellStyle(cellStyle);

        cell = row.createCell(colInt++, CellType.STRING); //罚时
        cell.setCellValue("罚时");
        cell.setCellStyle(cellStyle);
        if(rank.getUsers()==null||rank.getUsers().size()==0)throw new RetrunMsgException("榜单用户为空");
        List<ContestRecord> records = rank.getUsers().get(0).getRecords();  //题目列表
        for (int i = 0; i < records.size(); i++) {  //从 A 开始
            cell = row.createCell(colInt++,CellType.STRING);
            cell.setCellValue(String.valueOf((char) ('A'+i)));
            cell.setCellStyle(cellStyle);
        }
        for (RankUser user : rank.getUsers()) {
            colInt = 0;  //从头开始
            row = sheet.createRow(rowInt++);
            //名次
            cell = row.createCell(colInt++,CellType.NUMERIC);
            cell.setCellValue(user.getRanking());
            //名字
            cell = row.createCell(colInt++,CellType.STRING);
            cell.setCellValue(user.getUsername());
            //学校
            cell = row.createCell(colInt++,CellType.STRING);
            cell.setCellValue(user.getSchool());
            //通过题目数
            cell = row.createCell(colInt++,CellType.NUMERIC);
            cell.setCellValue(user.getAc());
            //罚时
            cell = row.createCell(colInt++,CellType.NUMERIC);
            cell.setCellValue(user.getTime()/60);  //分钟

            for (ContestRecord record : user.getRecords()) {
                cell = row.createCell(colInt++,CellType.STRING);
                StringBuilder val = new StringBuilder();
                if(record.getIsAc()!=null&&record.getIsAc()==0){  //通过
                    if(record.getIsFirst()!=0){  //一血
                        cell.setCellStyle(cellStyleFirst);
                    }else{
                        cell.setCellStyle(cellStyleAc);
                    }
                    val.append(record.getIsFirst()+1);  //通过次序
                    if(record.getSubmitSum()!=0){  //尝试次数
                        val.append("\n(").append(record.getSubmitSum()).append(")");

                    }
                }else if(record.getIsAc()!=null){  //错误
                    cell.setCellStyle(cellStyleWa);
                    val.append("-").append(record.getSubmitSum());
                }else {  //未提交
                    continue;
                }

                cell.setCellValue(val.toString());
            }
        }

        return wb;
    }

    /**
     * 导出实体类execl列表
     * @param clazz
     * @param data
     * @param <T>
     * @return
     * @throws IOException
     */
    public static <T> Workbook getExcel(Class<T> clazz,DataList<T> data) throws IOException {
        //工作簿
        Workbook wb = new SXSSFWorkbook();    //大数据
        //Workbook wb = new XSSFWorkbook();  //小数据

        Sheet sheet = wb.createSheet();

        //获取列名
        Field[] fields = clazz.getDeclaredFields();
        fields = getExist(fields);

        int rowInt = 0;
        //写入列名
        Row row = sheet.createRow(rowInt++);

        for (int i = 0; i < fields.length; i++) {
            Cell cell = row.createCell(i, CellType.STRING);
            ExcelCellName cellName = fields[i].getAnnotation(ExcelCellName.class);
            String name = null;
            if(cellName!=null && !MyUtils.isEmpty(cellName.value())){  //有注解名字写入注解名字
                name = cellName.value();
            }else name = fields[i].getName();         //写入属性名
            cell.setCellValue(name);
            fields[i].setAccessible(true);
        }
        List<T> results = null;
        while((results = data.nextList())!=null){
            for (int i = 0; i < results.size(); i++) {
                if(results.get(i)!=null){
                    row = sheet.createRow(rowInt++);  //创建行，从 上一个行开始
                    for (int j = 0; j < fields.length; j++) {
                        Object o = null;
                        try {
                            o = fields[j].get(results.get(i));
                            //System.out.println(i+";filed="+fields[j].getName()+" "+o);
                        } catch (IllegalAccessException e) {
                            log.warn("对象:\n{}\n属性: {}获取失败，失败信息：{}",results.get(i),fields[j].getName(),e.getMessage());
                        }
                        if(o!=null){
                            if(fields[j].getType()==Date.class){ //日期类型
                                String format = new SimpleDateFormat(pattern).format(o);
                                row.createCell(j,CellType.NUMERIC).setCellValue(format);
                            }
                            else {
                                row.createCell(j).setCellValue(o.toString());
                            }
                            //System.out.println("\t"+row.getRowNum()+","+j);
                        }
                    }
                }
            }
        }
        return wb;
    }
    public static abstract class DataList<T>{
        /**
         * 每次获取的数据来源
         * @param page 从 1 开始
         * @param size
         * @return
         */
        public abstract List<T> selectList(long page,short size) throws IOException;

        /**
         * 开始的分页
         */
        private long page = 1;
        /**
         * 默认分页的大小
         */
        private short size  = 10;



        public List<T> nextList() throws IOException {
            log.debug("读取数据page={},size={}",page,size);
            List<T> list = this.selectList(page++, size);
            if(list!=null&&list.size()!=0)return list;
            return null;
        }
        public void start(){
            page=1;
        }

    }
}
